Add grand totals and sub-totals to your grids to provide additional context and information. Totals can be enabled either through the right-click context menus or via the Totals dialog box which offers more options for setting and configuring the totaling logic deployed. The dialog is explained below.
- Click here to learn how to disable total formatting.
Using the Totals Dialog
Quick totals are normally enabled via the context menu and apply only to grid visualizations. The alternative technique is to turn on totals using the ribbon buttons and the associated dialogs. This offers finer control over both the total logic. Toggle the Totals button from the Home or Query ribbon to show or hide totals.
By default, grand totals will be added to the grid. Clicking on the bottom of the Totals split button, users can access more controls, options and settings for totaling columns and rows.
Basic Totals Settings
Using the bottom of the Totals split button will allow users to configure specific options for totals for columns and rows separately (green and blue arrows below).
Set Totals by Measure
Use the checkbox to enable totals functions by measure (yellow highlight above). You can then enable or disable totals by measure, and set the function type used to calculate totals for each measure.
In the example below, the Rank Sales measure was created via the Rank context calculation. We don't want the grand total to be included in the ranking. To exclude it, the total function for the Rank Sales measure was set to "None":
Total Position
- For Columns: options to show totals on the left or right.
- For Rows: options to show totals on the bottom or top.
Bottom Totals: adds an additional row or column at the end or bottom of the grid - this is the default position.
Top Totals: adds an additional row or column at the start or top of the grid.
Totals Type
Sub-totals cannot function together with sorting, filtering, N-of-N and elimination logic.
Grand Totals Only: displays grand totals only:
Grand and Sub-Totals: displays both grand totals and sub-totals:
Sub Totals: displays sub-totals only:
Sub-Totaling Options
If sub-totaling is enabled (red arrow below) and there are 2 or more attributes in the grid (blue and purple arrows below), a new set of options are available to control which attributes will be sub-totaled (green box below). By default all attributes are sub-totaled.
Total Functions
The totaling math logic is driven by this option for all metrics in the grid.
- Aggregate (default): uses the aggregation type defined within the source database.
- Sum: displays the sum of the totaled cells.
- Count: displays the number of contributing cells. Empty cells are NOT counted.
- Average: displays the average value of the contributing cells. Empty cells are NOT included in calculation.
- Minimum: displays the minimum value from among the contributing cells.
- Maximum: displays the maximum value from among the contributing cells
- Median: displays the median value of the contributing cells. Empty cells are NOT included in calculation.
- Standard Deviation: displays the standard deviation between the contributing cells. Empty are NOT included in calculation.
Logic Options
The way hierarchical data should total in MDX based engines is governed by the 'logic options' (green box below).
- Bottom-Up "Hierarchical" : When users total multi-level or 'regular' hierarchies, the engine will add up all the leaf level items from the bottom, ignoring any parent elements in the tree. This is the default "hierarchical" logic option. In some situations, this approach might not be appropriate or the desired effect.
- Top-Down Hierarchical: This will add all the nodes in a hierarchy starting from the top of the hierarchy working its way down, ignoring any child elements of existing parent objects in the tree.
- Flat: will add up all visible elements int eh tree, regardless of any ancestor or descendant objects.
Advanced Total Options
Advanced formulation options are available on MDX based data sources only. These switches are provided to control the complexities of totaling in MDX cube based technologies.
Formulation Options
Beyond the logic technique, there are a variety of other switches for totaling math in MDX engines (green box above):
- Ignore Model Scoping: this informs the query engine to ignore the 'scope isolation' switch in Microsoft OLAP. This changes the behavior of client side calculations vs server side calculations.
- Include Calculated Member: this informs to include calculated members in totals.
- Overwrite Solve Order: in situations where there are overlapping calculations, the order in which they are solved can impact the totals. This allows you to specify the solve order for the totals themselves.
- Intelligent Totals: this switch allows the engine to heuristically decide on the fastest way to add totals to your query. The switch allows you to turn it off in the event the approach makes things slower in your specific circumstance.
- Force Data Source Totals: The totaling engine will, by default, automatically determine if it needs to calculate totals on the data source engine or if it can be resolved accurately by Pyramid (which is faster). Check this box to override this logic and force all totals to be calculated on the data source only.
- Optimize Totals: This matches the settings that can be triggered from the query settings menu.
- Context Heuristics: applies 'EXISTING' functional logic when the attributes used in one of the totals derive from the same dimension of a Microsoft OLAP or Tabular model. For more information on the EXISTING function, please see here.
- Calculate in Sub Query: choose Sub Query from the Totals Method drop-down to split each of the totals combinations into multiple queries. Each query will then put the relevant members into the SUB QUERY of the MDX. This can sometimes greatly optimize the performance of the query. This does not work under the following circumstances:
- When 'Include Calculated Member' is on.
- When "Flat" is chosen from Logic Options.
- If any hierarchies in the query are Non-Aggregatable (have no ALL member)
Custom Captions
To create custom totals captions, open the Advanced panel and enter the custom captions:
